CREATE TABLE [dbo].[tb_LicenciasHistoricas]
(
[idLicenciaHistorica] [int] NOT NULL IDENTITY(1, 1),
[Agente] [int] NULL,
[Licencia] [int] NOT NULL,
[ConOSinGoce] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaAlta] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FechaBaja] [datetime] NOT NULL,
[Dias] [int] NOT NULL,
[Funcion] [int] NULL,
[FechaRegistro] [datetime] NULL CONSTRAINT [DF_tb_LicenciasHistoricas_FechaRegistro] DEFAULT (getdate()),
[CargadaPor] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tb_LicenciasHistoricas_CargadaPor] DEFAULT (suser_sname())
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[BajasLogicastb_LicenciasHistoricas] ON [dbo].[tb_LicenciasHistoricas]
FOR UPDATE AS
SET NOCOUNT ON
DECLARE @idLicenciaHistoricaAudit INT
if update(baja) and (Select baja from inserted)='S' and (Select FechaBaja from inserted) is null BEGIN
	UPDATE tb_LicenciasHistoricas set FechaBaja = getdate() where idLicenciaHistorica=(Select idLicenciaHistorica from inserted)
END
else
if (update(baja) and (Select baja from inserted)='N')
BEGIN
	UPDATE tb_LicenciasHistoricas set FechaBaja= null where idLicenciaHistorica=(Select idLicenciaHistorica from inserted)
    /*borra el registro duplicado en la tabla tb_LicenciasHistoricasAudit*/
   select @idLicenciaHistoricaAudit=max(idLicenciaHistoricaAudit) FROM  dbo.tb_LicenciasHistoricasAudit
   delete dbo.tb_LicenciasHistoricasAudit where idLicenciaHistoricaAudit=@idLicenciaHistoricaAudit
END
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [dbo].[tri_LicenciasHistoricasAudit] 
on [dbo].[tb_LicenciasHistoricas] for insert,update,delete as
DECLARE @OPERACION varchar(50)

IF EXISTS (SELECT * FROM INSERTED)
BEGIN
    IF EXISTS (SELECT * FROM DELETED)
    BEGIN
        --UPDATE
        SET @OPERACION='UPDATE'
    END
    ELSE
    BEGIN
        --INSERT
        SET @OPERACION='INSERT'
    END
    
    --handle insert or update data
 insert into tb_LicenciasHistoricasAudit(idlicenciahistorica,agente,licencia,conosingoce,fechaalta,baja,
                                             fechabaja,dias,funcion,fecharegistro,
										     cargadapor,operacion,fechaoperacion)
                                   SELECT  idlicenciahistorica,agente,licencia,conosingoce,fechaalta,baja,
                                             fechabaja,dias,funcion,fecharegistro,
										     cargadapor,@OPERACION,getdate() FROM inserted 

END
ELSE IF EXISTS(SELECT * FROM DELETED)
BEGIN
    --DELETE
    SET @OPERACION='DELETE'
     insert into tb_LicenciasHistoricasAudit(idlicenciahistorica,agente,licencia,conosingoce,fechaalta,baja,
                                             fechabaja,dias,funcion,fecharegistro,
										     cargadapor,operacion,fechaoperacion)
                                   SELECT  idlicenciahistorica,agente,licencia,conosingoce,fechaalta,baja,
                                             fechabaja,dias,funcion,fecharegistro,
										     cargadapor,@OPERACION,getdate() FROM deleted 
    
    

END


select *from tb_licenciasHistoricasAudit

select *from tb_licenciasHistoricas
GO
ALTER TABLE [dbo].[tb_LicenciasHistoricas] ADD CONSTRAINT [PK_tb_LicenciasHistoricas] PRIMARY KEY CLUSTERED  ([idLicenciaHistorica]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_LicenciasHistoricas] WITH NOCHECK ADD CONSTRAINT [FK_tb_LicenciasHistoricas_tb_Agentes] FOREIGN KEY ([Agente]) REFERENCES [dbo].[tb_Agentes] ([idAgente])
GO
ALTER TABLE [dbo].[tb_LicenciasHistoricas] WITH NOCHECK ADD CONSTRAINT [FK_tb_LicenciasHistoricas_tb_Licencias] FOREIGN KEY ([Licencia]) REFERENCES [dbo].[tb_Licencias] ([idLicencia])
GO
